#!/usr/bin/python

import os
import sys
import re
from optparse import OptionParser
import MySQLdb
from MySQLdb import OperationalError
from MySQLdb import ProgrammingError
from MySQLdb.constants import ER as SqlError
import hashlib

DEFAULT_DATABASE = "sqls"
DEFAULT_TABLE = "sqls"
DEFAULT_SQL_PARSER_RE = (r"(\'.*\').*(\'.*\')(\^.*\^)")
CREATE_DATABASE = '''
                    create database %s
                  '''

CREATE_TABLE = '''
                create table `%s` (
                    `id` varchar(36) not null, 
                    `start_time` double, 
                    `end_time` double, 
                    `name` TEXT, 
                    `sql_id` varchar(36) not null, 
                    `module` varchar(36) not null,
                    primary key (`id`)) 
                    ENGINE=InnoDB DEFAULT CHARSET=utf8 
               '''

DELETE_TABLE = '''
                    drop table `%s` 
               '''
DELETE_DATABASE = '''
                    drop database `%s` 
               '''
UPDATE_SQL = '''
                update `sqls` set 
                start_time=%f,
                name="%s",
                sql_id="%s" 
                where id="%s"
            '''

UPDATE_END_TIME_SQL = '''
                        update `sqls` set 
                        end_time=%f 
                        where id="%s"
                      ''' 
INSERT_SQL = '''
                insert into `sqls` values(
                '%s', %f, %f, '%s', '%s', '%s')
            ''' 
QUERY_SQL_ID = '''
                select id from `sqls` 
                where id="%s"
            '''

class DataBase(object):
    def __init__(self, 
                host=None, 
                port=None,
                user_name=None, 
                password=None):
        self.database = DEFAULT_DATABASE
        self.host=host
        self.port=port
        self.user=user_name
        self.password=password
        self.table = DEFAULT_TABLE
        self._connection = None

    def get_conn(self):
        if not self._connection:
            self._connection = MySQLdb.connect(host=self.host, 
                                               user=self.user, 
                                               passwd=self.password)
        return self._connection

    def get_cursor(self): 
        return self.get_conn().cursor()

    def cursor_close(self, cursor):
        if cursor != None:
            cursor.close()

    def execute(self, sql):
        cursor=self.get_cursor()
        ret=cursor.execute(sql)
        self.get_conn().commit()
        self.cursor_close(cursor)
        return ret

class SqlParse(object):

    def __init__(self, name=None, option_parser = OptionParser(),db=None):
        self.raw_sql_sets=[]
        self.sqls={}
        self.option_parser = option_parser
        self.db=db

    def parse_sql(self, res=None, sql=None):
        return re.findall(res, sql)

    def parse_sql_new(self, line, regex):
        parse_re=re.compile(regex)
        ret = re.findall(parse_re, line)
        id = ret[0][0].replace('\'',"")
        name = ret[0][1].replace('\'',"")
        timestamp = float(ret[0][2].replace('^',""))
        return id, name, timestamp

    def parse(self, log_file, module, regex):
        f=open(log_file)
        line=f.readline()

        while line:
            line=line.replace("\\n", " ")
            sql=None
            if " sql start(" in line and "SELECT 1" not in line:
                ( id, name, timestamp ) = self.parse_sql_new(line, regex)
                query_sql=( QUERY_SQL_ID % ( id ))
                get_sql=self.db.execute(query_sql)
                if get_sql:
                    sql = ( UPDATE_SQL % ( timestamp, 
                                           name, 
                                           hashlib.md5(name).hexdigest(), 
                                           id ))
                else:
                    sql = ( INSERT_SQL % ( id,
                                           timestamp, 
                                           0, 
                                           name, 
                                           hashlib.md5(name).hexdigest(), 
                                           module ))
            elif " sql end(" in line and "SELECT 1" not in line:
                ( id, name, timestamp ) = self.parse_sql_new(line, regex)
                sql = ( UPDATE_END_TIME_SQL % ( timestamp, id ))
            if sql:
                self.db.execute(sql)

            line=f.readline()

    def registry_options(self):
        self.option_parser.add_option( "-f", "--file", 
                                        help="Input parse file", 
                                        action="store", 
                                        type="string",
                                        dest="file" )
        self.option_parser.add_option( "-m", "--module", 
                                        help="Sql module name", 
                                        action="store", 
                                        type="string",
                                        dest="module" )
        self.option_parser.add_option( "-l", "--filelist", 
                                        help="Input parse file dir \
                                              and all file will be parsed",
                                        action="callback",
                                        callback=self.expand_path,
                                        dest="filelist",
                                        type="str" )
        self.option_parser.add_option( "-d", "--database", 
                                        help="spec database, default is sqls", 
                                        action="store",
                                        dest="database" )
        self.option_parser.add_option( "-t", "--table", 
                                        help=" create table default is sqls", 
                                        action="store",
                                        dest="table" )
        self.option_parser.add_option( "-c","--cleandb", 
                                        help=" cleandb table ", 
                                        action="store_true",
                                        dest="cleandb" )
        self.option_parser.add_option( "-r", "--regex", 
                                        help="regex of parser", 
                                        action="store",
                                        dest="regex" )
 

    def expand_path(self, option, opt, value, parser):
        filewalks=os.walk(value)
        filelist=[]
        for root, dirs, files in filewalks:
            for f in files:
                filelist.append(os.path.join(root, f))
                setattr(parser.values, option.dest, filelist)

    def db_init(self, values):
        database = values.database
        table = values.table
        if database:
            self.db.database = database
        if table:
            self.db.table = table
        try:
            if values.cleandb:
                self.db.execute( DELETE_DATABASE % ( self.db.database ))
            self.db.execute('use %s ' % ( self.db.database ))
            self.db.execute('desc %s' % ( self.db.table ))
        except OperationalError as e:
            if e.args[0] == SqlError.BAD_DB_ERROR or SqlError.DB_DROP_EXISTS:
                self.db.execute( CREATE_DATABASE % ( self.db.database ))
                self.db.execute('use %s ' % ( self.db.database ))
                self.db.execute( CREATE_TABLE % ( self.db.table ))
        except ProgrammingError as p:
            if p.args[0] == SqlError.NO_SUCH_TABLE:
                self.db.execute( CREATE_TABLE % ( self.db.table ))

if __name__ == "__main__":
    db = DataBase(
                    host='localhost', 
                    port='3306', 
                    user_name='root', 
                    password='aaaaaa' )
    sql_parse = SqlParse("log", db=db)
   #add command line options
    sql_parse.registry_options()    
    if len(sys.argv) <=1 :
        sql_parse.option_parser.print_help()
        sys.exit()
   
    #parser options command
    (values, remain_args) = sql_parse.option_parser.parse_args()
    sql_parse.db_init(values)
    if not values.module:
        print "Error: Module name is needed. "
        sys.exit(1)

    regex=values.regex
    if not regex:
        regex=DEFAULT_SQL_PARSER_RE

    if values.filelist and len(values.filelist):
        for f in values.filelist:
            sql_parse.parse(f, values.module, regex)
    elif values.file:
        sql_parse.parse(values.file, values.module, regex)
    else:
        print "Error: input file is needed. "
